import pandas as pd
import numpy as np
""" import sys
sys.path.append('D:/gitee/python-script-new/papertools/') """
from paper_tool.papertools import r2

def get_group_columns(column_names):
    groups = {}
    
    for column in column_names:
        parts = column.split('_')
        key = '_'.join(parts[:-1])  # 使用除最后一个单词外的部分作为键
        
        if key in groups:
            groups[key].append(column)
        else:
            groups[key] = [column]
    
    grouped_columns = list(groups.values())
    return grouped_columns

# 未调用
def data_plus_and_fill_V3(sheetTofill, means,num_rows=150, min_value=1, max_value=5, columns=None):

    if columns is None:
        columns = [f"Column_{i + 1}" for i in range(len(means))]

    grouped_columns = get_group_columns(columns)

    generated_data = []

    total_index = 0  
    for group_index, group in enumerate(grouped_columns):
        for item_index, column in enumerate(group):
            if item_index == 0:  # 当 item_index 为 0 时生成数据，否则赋空值
                while True:
                    data = r2.test_random_int(means[total_index], num_rows)
                    variance = np.var(data)
                    if variance != 0:
                        break
            else:
                data = [None] * num_rows  # 赋空值
            generated_data.append(data)
            total_index += 1

    df = pd.DataFrame(generated_data).T
    df.columns = columns

    return df


def find_column_index(column_names, data_sheet):

    all_columns = list(data_sheet.keys())
    for i, col_name in enumerate(all_columns):
        if col_name in column_names and data_sheet[col_name][9] and str(data_sheet[col_name][9]) != 'nan':
            column_index = column_names.index(col_name)
            # valid_columns.append((col_name, column_index, i))
            return {'col_name': col_name, 'column_index': column_index, 'index_in_dataset': i}
    # return valid_columns

def generate_column_data(original_data, original_mean, expected_mean, lower_limit=1, upper_limit=5):
    # 计算原始数据与预期平均数的差值
    diff_mean = expected_mean - original_mean

    # 计算需要调整的总量 , 四舍五入取整
    total_adjustment = round(len(original_data) * diff_mean)   

    if(total_adjustment == 0):
        return original_data

    new_column_data = r2.adjust_data_rhythm(original_data,total_adjustment,lower_limit,upper_limit)

    return new_column_data

file_path = 'dest/20231125-231918_truncatedNew.xlsx' 

# file_path = 'dest/20231129-135223_truncatedNew.xlsx'

sheet_filled_name = 'SheetFilled'

def main_fill_data_by_seed(file_path,init_sheet_name,sheet_seed_name,new_save_sheet_name):

    # 读取第一张sheet表的数据
    data_from_sheet1 = pd.read_excel(file_path, sheet_name=init_sheet_name)
    # iloc[0]是一个pandas库中的函数，用于获取数据框中的第一行数据。在这里，我们使用iloc[0]来获取Excel文件中第一行的数据。如果你想获取其他行的数据，只需将0替换为所需的行号即可
    mean_values = data_from_sheet1.iloc[0].values


    # 待填充数据的表
    data_from_sheet2 = pd.read_excel(file_path, sheet_name=sheet_seed_name)

    grouped_columns = get_group_columns(data_from_sheet1.columns)

    # 调用函数生成数据
    # generated_data = data_plus_and_fill_V3(mean_values,columns=data_from_sheet1.columns)

    total_index_global = 0
    current_col_data = None
    current_col_data_mean = 0

    for group_index, group in enumerate(grouped_columns):

        # 找到有数据的一列的索引
        seed_data = find_column_index(group,data_from_sheet2)      
        current_col_data = data_from_sheet2[seed_data['col_name']]
        current_col_data_mean = mean_values[seed_data['index_in_dataset']] 
        seed_index = seed_data['column_index']

        for item_index, column in enumerate(group): 
            # 取首列数据作为种子数据  
            """ if item_index == 0:
                current_col_data = data_from_sheet2[column]
                current_col_data_mean = mean_values[total_index_global] """      
            if item_index == seed_index:
                continue
            else :
                # item_mean_deviation = mean_values[total_index_global] - current_col_data_mean
                # 填充新值          
                data_from_sheet2[column] = generate_column_data(current_col_data,current_col_data_mean,mean_values[total_index_global])
            total_index_global += 1

    # 写入到同一个Excel文件的新Sheet表中

    with pd.ExcelWriter(file_path, engine='openpyxl', mode='a') as writer:
        # 将填充后的数据写入新的Sheet表"Filled_Data"
        data_from_sheet2.to_excel(writer, sheet_name=new_save_sheet_name, index=False) 

    print(f"Step3: DataSheet has been filled and saved to {new_save_sheet_name}")

